﻿
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

using Gola.VN.Scb.Web.Common.Entities;
using Gola.VN.Scb.Web.Dal.Utilities;

namespace Gola.VN.Scb.Web.Dal
{
    class SqlDao
    {
        private SqlConnection _sqlCnn;

        public SqlDao()
        {
            string sqlCnnStr = ConfigurationManager.ConnectionStrings["SCB"].ToString();
            sqlCnnStr = Security.Crypto.Decrypt(sqlCnnStr);

            _sqlCnn = new SqlConnection(sqlCnnStr);
        }

        #region products
        internal IEnumerable<ProductEntity> GetAllProducts()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.Product.GetAll);
                return DataTableHelper.GetProductEntities(datas);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        internal int InsertProduct(ProductEntity product)
        {
            using (SqlCommand sqlCmm = new SqlCommand(QueryConstants.Product.Insert))
            {
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.ProviderId, product.ProviderId);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.CategoryId, product.CategoryId);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Title, product.Title);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Description, product.Description);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Image, product.ImageUrl);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Price, product.Price);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Currency, product.Currency);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.Promotion, product.Promotion);
                sqlCmm.Parameters.AddWithValue("@" + QueryConstants.Product.OldPrice, product.LastPrice);

                return ExecuteNonQuery(sqlCmm);
            }
        }

        internal int UpdateProduct(ProductEntity product)
        {
            using (SqlCommand sqlCmm = new SqlCommand(QueryConstants.Product.Update))
            {
                sqlCmm.Parameters.AddWithValue("@ID", product.Id);
                sqlCmm.Parameters.AddWithValue("@VIEWCOUNT", product.ViewCount);

                return ExecuteNonQuery(sqlCmm);
            }
        }
        #endregion

        #region categories
        internal IEnumerable<CategoryEntity> GetCategoriesAsFlat()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.Category.GetAll);
                return DataTableHelper.GetCategoryEntities(datas);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        internal DataTable GetCategoriesAsTree()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.Category.GetCategoriesAsTree);
                return datas;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region provider
        internal IEnumerable<ProviderEntity> GetAllProviders()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.Provider.GetAll);
                return DataTableHelper.GetProviderEntities(datas);
            }
            catch (SqlException ex) { throw ex; }
        }


        #endregion

        #region sysinfo
        internal int GetSysInfo()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.SysInfo.GetSysInfo);
                if (datas != null && datas.Rows != null && datas.Rows.Count > 0)
                    return System.Convert.ToInt32(datas.Rows[0][0]);

                return -1;
            }
            catch (System.Exception ex) { throw ex; }
        }

        internal bool UpdateSysInfo()
        {
            try
            {
                int result = ExecuteNonQuery(QueryConstants.SysInfo.UpdateSysInfo);

                return result > 0 ? true : false;
            }
            catch { }

            return false;
        }
        #endregion

        #region location
        internal IEnumerable<LocationEntity> GetLocations()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.Location.GetAll);
                return DataTableHelper.GetLocationEntities(datas);
            }
            catch (System.Exception ex) { throw ex; }
        }
        #endregion

        #region searchhistory
        internal List<SearchHistoryEntity> GetSearchHistories()
        {
            try
            {
                DataTable datas = ExecuteQuery(QueryConstants.SearchHistory.GetAll);
                return DataTableHelper.GetSearchHistoryEntities(datas);
            }
            catch (System.Exception ex) { throw ex; }
        }

        internal int UpdateSearchHistory(SearchHistoryEntity dtoSearchHistory)
        {

            using (var command = new SqlCommand(QueryConstants.SearchHistory.UpdateSearchHistories))
            {
                command.Parameters.AddWithValue("@Text", dtoSearchHistory.Text);
                command.Parameters.AddWithValue("@Link", dtoSearchHistory.Link);
                return ExecuteNonQuery(command);
            }
        }

        #endregion

        #region helper methods
        private void ValidateSqlCnn()
        {
            if (_sqlCnn.State == ConnectionState.Closed)
                _sqlCnn.Open();
        }

        private void CloseSqlCnn()
        {
            _sqlCnn.Close();
        }

        private int ExecuteNonQuery(SqlCommand sqlCmm)
        {
            ValidateSqlCnn();

            sqlCmm.Connection = _sqlCnn;
            int effectedRows = sqlCmm.ExecuteNonQuery();

            CloseSqlCnn();

            return effectedRows;
        }

        private int ExecuteNonQuery(string queryString)
        {
            SqlCommand sqlCmm = new SqlCommand(queryString);
            return ExecuteNonQuery(sqlCmm);
        }

        private DataTable ExecuteQuery(string queryString)
        {
            ValidateSqlCnn();

            DataTable datas = new DataTable();
            using (SqlDataAdapter adapter = new SqlDataAdapter(queryString, _sqlCnn))
            {
                adapter.Fill(datas);
            }

            CloseSqlCnn();

            return datas;
        }

        private DataTable ExecuteQuery(SqlCommand sqlCmm)
        {
            ValidateSqlCnn();

            DataTable datas = new DataTable();
            sqlCmm.Connection = _sqlCnn;
            using (SqlDataAdapter adapter = new SqlDataAdapter(sqlCmm))
            {
                adapter.Fill(datas);
            }

            CloseSqlCnn();

            return datas;
        }
        #endregion
    }
}
